A relational database typically includes several tables with rows and column populated with data such as the CONTACT TABLE of FIG. 1A and the NEWSLETTER TABLE of FIG. 1B. The primary key field of a table typically refers to a column in the table which is unique to that table and which functions as an index for that table. The actual primary key value for each record of the table must contain data unique to that particular record. In FIG. 1A the CONTACT ID field is the primary key and in FIG. 1B the NEWSLETTER ID field is the primary key. Two tables can be linked together by another table called a linking table such as the CONTACT-NEWSLETTER LINKING TABLE of FIG. 1C. Since a linking table relates one table to another table, the term “relational database” is used to describe the resultant database.
A “foreign key” relationship exists between the CONTACT-NEWSLETTER LINKING TABLE of FIG. 1C and the CONTACT TABLE of FIG. 1A because the LINKING TABLE of FIG. 1C includes the primary key “CONTACT ID” from the CONTACT TABLE of FIG. 1A. Likewise, a “foreign key” relationship exists between the CONTACT-NEWSLETTER LINKING TABLE of FIG. 1C and the NEWSLETTER TABLE of FIG. 1B because the LINKING TABLE of FIG. 1C includes the primary key “NEWSLETTER ID” from the NEWSLETTER TABLE of FIG. 1B.
The CONTACT TABLE of FIG. 1A and the NEWSLETTER TABLE of FIG. 1B. are referred to as “parent tables” because they are the source of the primary keys which populate the LINKING TABLE of FIG. 1C. The LINKING TABLE of FIG. 1C is referred to as the “child table” since it contains the foreign keys which point back to the parent tables. The child table is dependent because its foreign key values are constrained to be values of the primary keys in the parent tables.
In relational databases, foreign keys are widely used in linking tables to establish a special referential relationship between two tables. However, foreign keys have several limitations in a real-world referential relationship between two tables. These limitations include no data definition support for allowing multiple parent tables and no data modification support for allowing multiple parent tables. Moreover, foreign keys do not provide version support.
What is needed is a mechanism and methodology for removing these limitations when using a foreign key to establish a relationship between tables.